package com.zygh.questionnairesurvey.server;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.IoUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.baomidou.mybatisplus.core.toolkit.ObjectUtils;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.zygh.questionnairesurvey.entity.*;
import com.zygh.questionnairesurvey.entity.vo.DetailsStatica;
import com.zygh.questionnairesurvey.entity.vo.UserInfoVO;
import com.zygh.questionnairesurvey.mapper.*;
import org.apache.commons.beanutils.BeanComparator;
import org.apache.commons.collections.ComparatorUtils;
import org.apache.commons.collections.comparators.ComparableComparator;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.*;

/**
 * @program: questionnaire-survey
 * @description: 导出Excel
 * @author: ID-Tang
 * @create: 2021-05-11 14:59
 **/
@Service
public class ExportExcelService {
    @Resource
    UserInfoMapper userInfoMapper;

    @Resource
    private QuestionnaireMapper questionnaireMapper;
    @Resource
    private QuestionnaireService questionnaireService;
    @Resource
    private DeptMapper deptMapper;
    @Resource
    private ProblemMapper problemMapper;
    @Resource
    private ProblemDetailsMapper problemDetailsMapper;

    /**
     * 导出excel
     *
     * @return 文件名
     */
    public String export(HttpServletResponse response, Integer state,
                         Integer deptId, Integer termStatus) throws IOException {
        // 通过工具类创建writer
        ExcelWriter writer = ExcelUtil.getWriter(true);
        // 设置只导出有别名的字段
        writer.setOnlyAlias(true);
        //导出文件名
        StringBuilder fileName = new StringBuilder();
        Integer pId = null;
        switch (state) {
            case 1:
                this.setAlias(writer, 1);
                //已填写警员
                List<UserInfoVO> fillIn = userInfoMapper.listByFillIn(deptId, termStatus);

                //补全部门名
                fillIn.forEach(userInfoVO -> {
                    if (0 != userInfoVO.getPid()) {
                        Dept dept = deptMapper.selectById(userInfoVO.getPid());
                        userInfoVO.setDeptName(dept.getName() + "-" + userInfoVO.getDeptName());
                    }
                });

                //补全文件名
                if (ObjectUtils.isNotNull(fillIn)) {
                    pId = fillIn.get(0).getPid();
                }
                this.setFileName(deptId, fillIn, pId, fileName);

                //排序
                this.sortList(fillIn, "deptName", true);

                // 合并单元格后的标题行，使用默认标题样式
                writer.merge(3, fileName + "已填写警员信息");
                // 一次性写出内容，使用默认样式，强制输出标题
                writer.write(fillIn, true);
                //设置列宽,-1为默认所有列宽
                writer.setColumnWidth(-1, 20);
                //设置行高
                writer.setDefaultRowHeight(23);
                fileName.append("已填写警员信息.xlsx");
                break;
            case 2:
                //未填写警员
                List<UserInfoVO> noFillIn = userInfoMapper.listByNoFillIn(deptId, termStatus);
                this.setAlias(writer, 2);

                //补全部门名
                noFillIn.forEach(userInfoVO -> {
                    if (0 != userInfoVO.getPid()) {
                        Dept dept = deptMapper.selectById(userInfoVO.getPid());
                        userInfoVO.setDeptName(dept.getName() + "-" + userInfoVO.getDeptName());
                    }
                });

                //补全文件名
                if (ObjectUtils.isNotNull(noFillIn)) {
                    pId = noFillIn.get(0).getPid();
                }
                this.setFileName(deptId, noFillIn, pId, fileName);

                //排序
                this.sortList(noFillIn, "deptName", true);

                // 合并单元格后的标题行，使用默认标题样式
                writer.merge(3, fileName + "未填写警员信息");
                // 一次性写出内容，使用默认样式，强制输出标题
                writer.write(noFillIn, true);
                //设置列宽,-1为默认所有列宽
                writer.setColumnWidth(-1, 20);
                //设置行高
                writer.setDefaultRowHeight(23);
                fileName.append("未填写警员信息.xlsx");
                break;
            case 3:
                //已填写有问题警员
                List<UserInfoVO> problem = userInfoMapper.listByProblem(deptId, termStatus);
                this.setAlias(writer, 3);

                //补全部门名
                problem.forEach(userInfoVO -> {
                    if (0 != userInfoVO.getPid()) {
                        Dept dept = deptMapper.selectById(userInfoVO.getPid());
                        userInfoVO.setDeptName(dept.getName() + "-" + userInfoVO.getDeptName());
                    }
                });

                //补全文件名
                if (ObjectUtils.isNotNull(problem)) {
                    pId = problem.get(0).getPid();
                }
                this.setFileName(deptId, problem, pId, fileName);

                //排序
                this.sortList(problem, "deptName", true);

                // 合并单元格后的标题行，使用默认标题样式
                writer.merge(4, fileName + "已填写有问题警员信息");
                // 一次性写出内容，使用默认样式，强制输出标题
                writer.write(problem, true);
                //设置列宽,-1为默认所有列宽
                writer.setColumnWidth(-1, 20);
                //设置行高
                writer.setDefaultRowHeight(23);
                fileName.append("已填写有问题警员信息.xlsx");
                break;
            case 4:
                //已填写无问题警员
                List<UserInfoVO> noProblem = userInfoMapper.listByNoProblem(deptId, termStatus);
                this.setAlias(writer, 4);

                //补全部门名
                noProblem.forEach(userInfoVO -> {
                    if (0 != userInfoVO.getPid()) {
                        Dept dept = deptMapper.selectById(userInfoVO.getPid());
                        userInfoVO.setDeptName(dept.getName() + "-" + userInfoVO.getDeptName());
                    }
                });

                //补全文件名
                if (ObjectUtils.isNotNull(noProblem)) {
                    pId = noProblem.get(0).getPid();
                }
                this.setFileName(deptId, noProblem, pId, fileName);

                //排序
                this.sortList(noProblem, "deptName", true);

                // 合并单元格后的标题行，使用默认标题样式
                writer.merge(3, fileName + "已填写无问题警员");
                // 一次性写出内容，使用默认样式，强制输出标题
                writer.write(noProblem, true);
                //设置列宽,-1为默认所有列宽
                writer.setColumnWidth(-1, 25);
                //设置行高
                writer.setDefaultRowHeight(23);
                fileName.append("已填写无问题警员信息.xlsx");
                break;
            default:
                break;
        }

        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition",
                "attachment;filename=\"" + URLEncoder.encode(fileName.toString(), "UTF-8") + "\"");
        ServletOutputStream out = response.getOutputStream();

        // 关闭writer，释放内存
        writer.flush(out, true);
        writer.close();
        IoUtil.close(out);

        return fileName.toString();
    }


    /**
     * 自查事项填报表填写统计
     *
     * @param response response
     * @param id       部门id
     * @return String
     */
    public String exprot(HttpServletResponse response, Integer id) throws IOException {
        if (id == null) {
            id = 0;
        }
        // 通过工具类创建writer
        ExcelWriter writer = ExcelUtil.getWriter(true);
        // 设置只导出有别名的字段
        writer.setOnlyAlias(true);
        // 设置所有列为自动宽度
        writer.autoSizeColumnAll();
        ArrayList<Map<String, Object>> rows = CollUtil.newArrayList();
        //获取单位信息
        List<Dept> depts = deptMapper.selectList(Wrappers.<Dept>lambdaQuery()
                .eq(Dept::getPid, id)
                .orderByAsc(Dept::getSort));
        Map<String, Object> row2 = new LinkedHashMap<>();
        Map<String, Object> row3 = new LinkedHashMap<>();
        if (id == 0) {
            String num3 = NumberUtil.decimalFormat("#.##%", NumberUtil.div(questionnaireMapper.count2(null), questionnaireMapper.count1(null)));
            String num6 = NumberUtil.roundStr(NumberUtil.div(questionnaireMapper.count5(null), questionnaireMapper.count1(null)), 2);
            String counts = NumberUtil.roundStr(NumberUtil.div(questionnaireMapper.count8(null), questionnaireMapper.count7(null)), 2);

            row2.put("单位/项目", "交警支队");
            row2.put("应填报人数", questionnaireMapper.count1(null));
            row2.put("填报人数", questionnaireMapper.count2(null));
            row2.put("未填报人数", questionnaireMapper.count9(null));
            row2.put("填报率", num3);
            row2.put("未自查出问题人数", questionnaireMapper.count4(null));
            row2.put("单位问题条数", questionnaireMapper.count5(null));
            row2.put("单位人均问题条数", num6);
            row2.put("班子人数", questionnaireMapper.count7(null));
            row2.put("班子问题条数", questionnaireMapper.count8(null));
            row2.put("班子人均问题条数", counts);
            row2.put("备注", "无");


            rows.add(row2);
        }
        if (id != 0) {
            int[] child = questionnaireService.findChild(id);
            String num3 = "0";
            String num6 = "0";
            String counts = "0";
            if (questionnaireMapper.count2(id) + child[2] != 0 && questionnaireMapper.count1(id) + child[1] != 0) {
                num3 = NumberUtil.decimalFormat("#.##%", NumberUtil.div(questionnaireMapper.count2(id) + child[2], questionnaireMapper.count1(id) + child[1]));
            }
            if (questionnaireMapper.count5(id) + child[5] != 0 && questionnaireMapper.count1(id) + child[1] != 0) {
                num6 = NumberUtil.roundStr(NumberUtil.div(questionnaireMapper.count5(id) + child[5], questionnaireMapper.count1(id) + child[1]), 2);

            }
            if (questionnaireMapper.count8(id) + child[8] != 0 && questionnaireMapper.count7(id) + child[7] != 0) {
                counts = NumberUtil.roundStr(NumberUtil.div(questionnaireMapper.count8(id) + child[8], questionnaireMapper.count7(id) + child[7]), 2);

            }
            Dept dept = deptMapper.selectById(id);
            row3.put("单位/项目", dept.getName());
            row3.put("应填报人数", Double.valueOf(questionnaireMapper.count1(id) + child[1]));
            row3.put("填报人数", Double.valueOf(questionnaireMapper.count2(id) + child[2]));
            row3.put("未填报人数", Double.valueOf(questionnaireMapper.count9(id) + child[9]));
            row3.put("填报率", num3);
            row3.put("未自查出问题人数", Double.valueOf(questionnaireMapper.count4(id) + child[4]));
            row3.put("单位问题条数", Double.valueOf(questionnaireMapper.count5(id) + child[5]));
            row3.put("单位人均问题条数", Double.valueOf(num6));
            row3.put("班子人数", Double.valueOf(questionnaireMapper.count7(id) + child[7]));
            row3.put("班子问题条数", Double.valueOf(questionnaireMapper.count8(id) + child[8]));
            row3.put("班子人均问题条数", Double.valueOf(counts));
            row3.put("备注", "无");
            rows.add(row3);
        }
        depts.forEach(dept -> {
            //查询子级
            int[] child = questionnaireService.findChild(dept.getDeptId());
            String num3 = "0";
            String num6 = "0";
            String counts = "0";

            if (questionnaireMapper.count2(dept.getDeptId()) + child[2] != 0 && questionnaireMapper.count1(dept.getDeptId()) + child[1] != 0) {
                num3 = NumberUtil.decimalFormat("#.##%", NumberUtil.div(questionnaireMapper.count2(dept.getDeptId()) + child[2], questionnaireMapper.count1(dept.getDeptId()) + child[1]));
            }
            if (questionnaireMapper.count5(dept.getDeptId()) + child[5] != 0 && questionnaireMapper.count1(dept.getDeptId()) + child[1] != 0) {
                num6 = NumberUtil.roundStr(NumberUtil.div(questionnaireMapper.count5(dept.getDeptId()) + child[5], questionnaireMapper.count1(dept.getDeptId()) + child[1]), 2);

            }
            if (questionnaireMapper.count8(dept.getDeptId()) + child[8] != 0 && questionnaireMapper.count7(dept.getDeptId()) + child[7] != 0) {
                counts = NumberUtil.roundStr(NumberUtil.div(questionnaireMapper.count8(dept.getDeptId()) + child[8], questionnaireMapper.count7(dept.getDeptId()) + child[7]), 2);

            }

            Map<String, Object> row1 = new LinkedHashMap<>();
            row1.put("单位/项目", dept.getName());
            row1.put("应填报人数", questionnaireMapper.count1(dept.getDeptId()) + child[1]);
            row1.put("填报人数", questionnaireMapper.count2(dept.getDeptId()) + child[2]);
            row1.put("未填报人数", questionnaireMapper.count9(dept.getDeptId()) + child[9]);
            row1.put("填报率", num3);
            row1.put("未自查出问题人数", questionnaireMapper.count4(dept.getDeptId()) + child[4]);
            row1.put("单位问题条数", questionnaireMapper.count5(dept.getDeptId()) + child[5]);
            row1.put("单位人均问题条数", num6);
            row1.put("班子人数", questionnaireMapper.count7(dept.getDeptId()) + child[7]);
            row1.put("班子问题条数", questionnaireMapper.count8(dept.getDeptId()) + child[8]);
            row1.put("班子人均问题条数", Double.valueOf(counts));
            row1.put("备注", "无");

            rows.add(row1);
        });


        String fileName = System.currentTimeMillis() + ".xlsx";
        // 合并单元格后的标题行，使用默认标题样式
        writer.merge(11, "交警支队教育整顿顽疾填报统计表");
        // 一次性写出内容，使用默认样式，强制输出标题
        writer.write(rows, true);
        writer.setColumnWidth(-1, 15);
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition",
                "attachment;filename=\"" + URLEncoder.encode(fileName, "UTF-8") + "\"");
        ServletOutputStream out = response.getOutputStream();

        // 关闭writer，释放内存
        writer.flush(out, true);
        writer.close();
        IoUtil.close(out);

        return fileName;

    }

    /**
     * 自查事项填报表填写统计全部人员，民警+工勤
     *
     * @param response response
     * @param id       部门id
     * @return String
     */
    public String exprotAll(HttpServletResponse response, Integer id) throws IOException {
        if (id == null) {
            id = 0;
        }
        // 通过工具类创建writer
        ExcelWriter writer = ExcelUtil.getWriter(true);
        // 设置只导出有别名的字段
        writer.setOnlyAlias(true);
        // 设置所有列为自动宽度
        writer.autoSizeColumnAll();
        ArrayList<Map<String, Object>> rows = CollUtil.newArrayList();
        //获取单位信息
        List<Dept> depts = deptMapper.selectList(Wrappers.<Dept>lambdaQuery()
                .eq(Dept::getPid, id)
                .orderByAsc(Dept::getSort));
        Map<String, Object> row2 = new LinkedHashMap<>();
        Map<String, Object> row3 = new LinkedHashMap<>();
        if (id == 0) {
            String num3 = NumberUtil.decimalFormat("#.##%", NumberUtil.div(questionnaireMapper.count2All(null), questionnaireMapper.count1All(null)));
            String num6 = NumberUtil.roundStr(NumberUtil.div(questionnaireMapper.count5All(null), questionnaireMapper.count1All(null)), 2);
            String counts = NumberUtil.roundStr(NumberUtil.div(questionnaireMapper.count8All(null), questionnaireMapper.count7All(null)), 2);

            row2.put("单位/项目", "交警支队");
            row2.put("应填报人数", questionnaireMapper.count1All(null));
            row2.put("填报人数", questionnaireMapper.count2All(null));
            row2.put("未填报人数", questionnaireMapper.count9All(null));
            row2.put("填报率", num3);
            row2.put("未自查出问题人数", questionnaireMapper.count4All(null));
            row2.put("单位问题条数", questionnaireMapper.count5All(null));
            row2.put("单位人均问题条数", num6);
            row2.put("班子人数", questionnaireMapper.count7All(null));
            row2.put("班子问题条数", questionnaireMapper.count8All(null));
            row2.put("班子人均问题条数", counts);
            row2.put("备注", "无");


            rows.add(row2);
        }
        if (id != 0) {
            int[] child = questionnaireService.findChildAll(id);
            String num3 = "0";
            String num6 = "0";
            String counts = "0";
            if (questionnaireMapper.count2All(id) + child[2] != 0 && questionnaireMapper.count1All(id) + child[1] != 0) {
                num3 = NumberUtil.decimalFormat("#.##%", NumberUtil.div(questionnaireMapper.count2All(id) + child[2], questionnaireMapper.count1All(id) + child[1]));
            }
            if (questionnaireMapper.count5All(id) + child[5] != 0 && questionnaireMapper.count1All(id) + child[1] != 0) {
                num6 = NumberUtil.roundStr(NumberUtil.div(questionnaireMapper.count5All(id) + child[5], questionnaireMapper.count1All(id) + child[1]), 2);

            }
            if (questionnaireMapper.count8All(id) + child[8] != 0 && questionnaireMapper.count7All(id) + child[7] != 0) {
                counts = NumberUtil.roundStr(NumberUtil.div(questionnaireMapper.count8All(id) + child[8], questionnaireMapper.count7All(id) + child[7]), 2);

            }
            Dept dept = deptMapper.selectById(id);
            row3.put("单位/项目", dept.getName());
            row3.put("应填报人数", questionnaireMapper.count1All(id) + child[1]);
            row3.put("填报人数", questionnaireMapper.count2All(id) + child[2]);
            row3.put("未填报人数", questionnaireMapper.count9All(id) + child[9]);
            row3.put("填报率", num3);
            row3.put("未自查出问题人数", questionnaireMapper.count4All(id) + child[4]);
            row3.put("单位问题条数", questionnaireMapper.count5All(id) + child[5]);
            row3.put("单位人均问题条数", num6);
            row3.put("班子人数", questionnaireMapper.count7All(id) + child[7]);
            row3.put("班子问题条数", questionnaireMapper.count8All(id) + child[8]);
            row3.put("班子人均问题条数", counts);
            row3.put("备注", "无");
            rows.add(row3);
        }
        depts.forEach(dept -> {
            //查询子级
            int[] child = questionnaireService.findChildAll(dept.getDeptId());
            String num3 = "0";
            String num6 = "0";
            String counts = "0";

            if (questionnaireMapper.count2All(dept.getDeptId()) + child[2] != 0 && questionnaireMapper.count1All(dept.getDeptId()) + child[1] != 0) {
                num3 = NumberUtil.decimalFormat("#.##%", NumberUtil.div(questionnaireMapper.count2All(dept.getDeptId()) + child[2], questionnaireMapper.count1All(dept.getDeptId()) + child[1]));
            }
            if (questionnaireMapper.count5All(dept.getDeptId()) + child[5] != 0 && questionnaireMapper.count1All(dept.getDeptId()) + child[1] != 0) {
                num6 = NumberUtil.roundStr(NumberUtil.div(questionnaireMapper.count5All(dept.getDeptId()) + child[5], questionnaireMapper.count1All(dept.getDeptId()) + child[1]), 2);

            }
            if (questionnaireMapper.count8All(dept.getDeptId()) + child[8] != 0 && questionnaireMapper.count7All(dept.getDeptId()) + child[7] != 0) {
                counts = NumberUtil.roundStr(NumberUtil.div(questionnaireMapper.count8All(dept.getDeptId()) + child[8], questionnaireMapper.count7All(dept.getDeptId()) + child[7]), 2);

            }

            Map<String, Object> row1 = new LinkedHashMap<>();
            row1.put("单位/项目", dept.getName());
            row1.put("应填报人数", questionnaireMapper.count1All(dept.getDeptId()) + child[1]);
            row1.put("填报人数", questionnaireMapper.count2All(dept.getDeptId()) + child[2]);
            row1.put("未填报人数", questionnaireMapper.count9All(dept.getDeptId()) + child[9]);
            row1.put("填报率", num3);
            row1.put("未自查出问题人数", questionnaireMapper.count4All(dept.getDeptId()) + child[4]);
            row1.put("单位问题条数", questionnaireMapper.count5All(dept.getDeptId()) + child[5]);
            row1.put("单位人均问题条数", num6);
            row1.put("班子人数", questionnaireMapper.count7All(dept.getDeptId()) + child[7]);
            row1.put("班子问题条数", questionnaireMapper.count8All(dept.getDeptId()) + child[8]);
            row1.put("班子人均问题条数", Double.valueOf(counts));
            row1.put("备注", "无");

            rows.add(row1);
        });


        String fileName = System.currentTimeMillis() + ".xlsx";
        // 合并单元格后的标题行，使用默认标题样式
        writer.merge(11, "交警支队教育整顿顽疾填报统计表");
        // 一次性写出内容，使用默认样式，强制输出标题
        writer.write(rows, true);
        writer.setColumnWidth(-1, 15);
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition",
                "attachment;filename=\"" + URLEncoder.encode(fileName, "UTF-8") + "\"");
        ServletOutputStream out = response.getOutputStream();

        // 关闭writer，释放内存
        writer.flush(out, true);
        writer.close();
        IoUtil.close(out);

        return fileName;

    }


    /**
     * 导出统计工勤人员
     *
     * @param response response
     * @return 地址
     */
    public String worker(HttpServletResponse response, Integer id) throws IOException {
        if (id == null) {
            id = 0;
        }
        // 通过工具类创建writer
        ExcelWriter writer = ExcelUtil.getWriter(true);
        // 设置只导出有别名的字段
        writer.setOnlyAlias(true);
        List<Dept> deptByWord = null;
        //查询所有父级部门
        if (id == 0) {
            deptByWord = deptMapper.findDeptByWord(id);
        } else {
            deptByWord = deptMapper.findDeptWordById(id);
        }


        String znum3 = NumberUtil.decimalFormat("#.##%", NumberUtil.div(questionnaireMapper.workcount2(null), questionnaireMapper.workcount1(null)));
        String znum6 = NumberUtil.roundStr(NumberUtil.div(questionnaireMapper.workcount5(null), questionnaireMapper.workcount1(null)), 2);


        ArrayList<Map<String, Object>> rows = CollUtil.newArrayList();
        Map<String, Object> row2 = new LinkedHashMap<>();
        Map<String, Object> row3 = new LinkedHashMap<>();
        if (id == 0) {
            row2.put("单位/项目", "交警支队");
            row2.put("应填报人数", questionnaireMapper.workcount1(null));
            row2.put("填报人数", questionnaireMapper.workcount2(null));
            row2.put("未填报人数", questionnaireMapper.workcount9(null));
            row2.put("填报率", znum3);
            row2.put("未自查出问题人数", questionnaireMapper.workcount4(null));
            row2.put("单位问题条数", questionnaireMapper.workcount5(null));
            row2.put("单位人均问题条数", znum6);
            row2.put("备注", "无");

            rows.add(row2);
        }

        if (id != 0) {
            int[] workchild = questionnaireService.findWorkChild(id);
            String num3 = "0";
            String num6 = "0";
            if (questionnaireMapper.workcount2(id) + workchild[2] != 0) {
                num3 = NumberUtil.decimalFormat("#.##%", NumberUtil.div(questionnaireMapper.workcount2(id) + workchild[2], questionnaireMapper.workcount1(id) + workchild[1]));
            }
            if (questionnaireMapper.workcount5(id) + workchild[5] != 0 && questionnaireMapper.workcount1(id) + workchild[1] != 0) {
                num6 = NumberUtil.roundStr(NumberUtil.div(questionnaireMapper.workcount5(id) + workchild[5], questionnaireMapper.workcount1(id) + workchild[1]), 2);

            }
            Dept dept = deptMapper.selectById(id);
            row3.put("单位/项目", dept.getName());
            row3.put("应填报人数", questionnaireMapper.workcount1(id) + workchild[1]);
            row3.put("填报人数", questionnaireMapper.workcount2(id) + workchild[2]);
            row3.put("未填报人数", questionnaireMapper.workcount9(id) + workchild[9]);
            row3.put("填报率", num3);
            row3.put("未自查出问题人数", questionnaireMapper.workcount4(id) + workchild[4]);
            row3.put("单位问题条数", questionnaireMapper.workcount5(id) + workchild[5]);
            row3.put("单位人均问题条数", num6);
            row3.put("备注", "无");
            rows.add(row3);
        }

        deptByWord.forEach(dept -> {
            int[] workChild = questionnaireService.findWorkChild(dept.getDeptId());
            String num3 = "0";
            String num6 = "0";

            if (questionnaireMapper.workcount2(dept.getDeptId()) + workChild[2] != 0 && questionnaireMapper.workcount1(dept.getDeptId()) + workChild[1] != 0) {
                num3 = NumberUtil.decimalFormat("#.##%", NumberUtil.div(questionnaireMapper.workcount2(dept.getDeptId()) + workChild[2], questionnaireMapper.workcount1(dept.getDeptId()) + workChild[1]));
            }
            if (questionnaireMapper.workcount5(dept.getDeptId()) + workChild[5] != 0 && questionnaireMapper.workcount1(dept.getDeptId()) + workChild[1] != 0) {
                num6 = NumberUtil.roundStr(NumberUtil.div(questionnaireMapper.workcount5(dept.getDeptId()) + workChild[5], questionnaireMapper.workcount1(dept.getDeptId()) + workChild[1]), 2);
            }
            Map<String, Object> row1 = new LinkedHashMap<>();
            row1.put("单位/项目", dept.getName());
            row1.put("应填报人数", questionnaireMapper.workcount1(dept.getDeptId()) + workChild[1]);
            row1.put("填报人数", questionnaireMapper.workcount2(dept.getDeptId()) + workChild[2]);
            row1.put("未填报人数", questionnaireMapper.workcount9(dept.getDeptId()) + workChild[9]);
            row1.put("填报率", num3);
            row1.put("未自查出问题人数", questionnaireMapper.workcount4(dept.getDeptId()) + workChild[4]);
            row1.put("单位问题条数", questionnaireMapper.workcount5(dept.getDeptId()) + workChild[5]);
            row1.put("单位人均问题条数", num6);
            row1.put("备注", "无");

            rows.add(row1);
        });
        String fileName = System.currentTimeMillis() + ".xlsx";
        // 合并单元格后的标题行，使用默认标题样式
        writer.merge(8, "工勤人员教育整顿顽疾填报统计表");
        // 一次性写出内容，使用默认样式，强制输出标题
        writer.write(rows, true);
        writer.setColumnWidth(-1, 15);
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition",
                "attachment;filename=\"" + URLEncoder.encode(fileName, "UTF-8") + "\"");
        ServletOutputStream out = response.getOutputStream();

        // 关闭writer，释放内存
        writer.flush(out, true);
        writer.close();
        IoUtil.close(out);

        return fileName;
    }

    /**
     * 通过问题id导出人员信息
     *
     * @param response response
     * @param lowest   问题id
     * @return 文件名
     */
    public String exportByLowest(HttpServletResponse response, Integer lowest, Integer termStatus, Integer deptId) {
        // 通过工具类创建writer
        ExcelWriter writer = ExcelUtil.getWriter(true);
        // 设置只导出有别名的字段
        writer.setOnlyAlias(true);
        Problem problem = problemMapper.selectById(lowest);
        Dept byId = deptMapper.selectById(deptId);
        //导出文件名
        String fileName = (null == deptId) ? problem.getName() + ".xlsx"
                : byId.getName() + "-" + problem.getName() + ".xlsx";

        List<UserInfo> userInfos = questionnaireMapper.listPeople(deptId, lowest, termStatus);
        userInfos.forEach(userInfo -> userInfo
                .setDeptName(userInfo.getPName() != null
                        ? userInfo.getPName() + "-" + userInfo.getDeptName()
                        : userInfo.getDeptName()));

        List list = this.sortList(userInfos, "deptName", true);

        //自定义标题别名
        writer.addHeaderAlias("deptName", "中队");
        writer.addHeaderAlias("name", "警员姓名");
        writer.addHeaderAlias("userName", "警号");

        // 合并单元格后的标题行，使用默认标题样式
        writer.merge(2, fileName.replaceAll(".xlsx", ""));
        // 一次性写出内容，使用默认样式，强制输出标题
        writer.write(list, true);
        //设置列宽,-1为默认所有列宽
        writer.setColumnWidth(-1, 25);
        //设置行高
        writer.setDefaultRowHeight(23);

        ServletOutputStream out = null;
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            response.setHeader("Content-Disposition",
                    "attachment;filename=\"" + URLEncoder.encode(fileName, "UTF-8") + "\"");
            out = response.getOutputStream();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            // 关闭writer，释放内存
            if (out != null) {
                writer.flush(out, true);
            }
            writer.close();
            IoUtil.close(out);
        }


        return fileName;
    }

    /**
     * 导出人员信息
     *
     * @param response response
     * @param id       部门id
     * @return String
     */
    public String exportUser(HttpServletResponse response, Integer id, Integer termStatus) throws IOException {
        // 通过工具类创建writer
        ExcelWriter writer = ExcelUtil.getWriter(true);
        // 设置只导出有别名的字段
        writer.setOnlyAlias(true);
        List<UserInfo> userInfo = userInfoMapper.findUserInfoByType(id, termStatus);
        ArrayList<Map<String, Object>> rows = CollUtil.newArrayList();
        userInfo.forEach(userInfo1 -> {
            Map<String, Object> row1 = new LinkedHashMap<>();
            row1.put("部门", userInfo1.getDeptName());
            row1.put("人员名称", userInfo1.getName());
            row1.put("警号", userInfo1.getUserName());
            row1.put("类型", userInfo1.getTermName());

            rows.add(row1);
        });

        String fileName = System.currentTimeMillis() + ".xlsx";
        // 合并单元格后的标题行，使用默认标题样式
        writer.merge(3, "人员信息表");
        // 一次性写出内容，使用默认样式，强制输出标题
        writer.write(rows, true);
        writer.setColumnWidth(-1, 15);
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition",
                "attachment;filename=\"" + URLEncoder.encode(fileName, "UTF-8") + "\"");
        ServletOutputStream out = response.getOutputStream();
        // 关闭writer，释放内存
        writer.flush(out, true);
        writer.close();
        IoUtil.close(out);

        return fileName;
    }


    /**
     * 根据部门导出单位问题
     *
     * @param response
     * @param id
     * @return
     */
    public String exportProblem(HttpServletResponse response, Integer id, String encoder, Integer termStatus, String warningsignal, String name) throws IOException {
        // 通过工具类创建writer
        ExcelWriter writer = ExcelUtil.getWriter(true);
        // 设置只导出有别名的字段
        writer.setOnlyAlias(true);

        ArrayList<Map<String, Object>> rows = CollUtil.newArrayList();
        /**
         * 查询问题
         */
        List<ProblemDetails> problemDetails = userInfoMapper.finUserByDetails(id, encoder, termStatus, warningsignal, name);
        problemDetails.forEach(problemDetails1 -> {
            Map<String, Object> row1 = new LinkedHashMap<>();
            Dept dept = deptMapper.selectById(problemDetails1.getDeptId());
            if (dept.getPid() != 0) {
                Dept dept1 = deptMapper.selectOne(Wrappers.<Dept>lambdaQuery().eq(Dept::getDeptId, dept.getPid()));
                row1.put("部门", dept1.getName() + "-" + problemDetails1.getDeptName());
            } else {
                row1.put("部门", problemDetails1.getWarningsignal());
            }
            row1.put("人员名称", problemDetails1.getName());
            row1.put("警号", problemDetails1.getWarningsignal());
            row1.put("问题编号", problemDetails1.getEncoder());
            row1.put("问题描述", problemDetails1.getProblemName());
            row1.put("问题详情", problemDetails1.getDetails());

            rows.add(row1);
        });


        //集合排序
        rows.sort((o1, o2) -> {
            String name1 = String.valueOf(o1.get("部门").toString());//name1是从你list里的第一个name
            String name2 = String.valueOf(o2.get("部门").toString()); //name2是从你list里的第二个name
            return name1.compareTo(name2);
        });

        String fileName = System.currentTimeMillis() + ".xlsx";
        // 合并单元格后的标题行，使用默认标题样式
        writer.merge(6, "人员信息表");
        // 一次性写出内容，使用默认样式，强制输出标题
        writer.write(rows, true);
        writer.setColumnWidth(-1, 23);
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition",
                "attachment;filename=\"" + URLEncoder.encode(fileName, "UTF-8") + "\"");
        ServletOutputStream out = response.getOutputStream();
        // 关闭writer，释放内存
        writer.flush(out, true);
        writer.close();
        IoUtil.close(out);
        return fileName;
    }

    public String findDetailStaticeById(HttpServletResponse response, Integer id, Integer termStatus) throws IOException {
        // 通过工具类创建writer
        ExcelWriter writer = ExcelUtil.getWriter(true);
        // 设置只导出有别名的字段
        writer.setOnlyAlias(true);
        ArrayList<Map<String, Object>> rows = CollUtil.newArrayList();
        //有问题人数
        List<DetailsStatica> detailStaticeById = problemDetailsMapper.findDetailStaticeById(id, termStatus);
        detailStaticeById.forEach(detailsStatica -> {
            Map<String, Object> row1 = new LinkedHashMap<>();
            Dept dept = deptMapper.selectOne(Wrappers.<Dept>lambdaQuery().eq(Dept::getDeptId, detailsStatica.getPid()));
            if (dept != null) {
                row1.put("单位", dept.getName() + "-" + detailsStatica.getDeptName());
            } else {
                row1.put("单位", detailsStatica.getDeptName());
            }
            row1.put("姓名", detailsStatica.getName());
            row1.put("警号", detailsStatica.getWarningsignal());
            row1.put("选项数量", problemMapper.findProblemCoundById(detailsStatica.getWarningsignal()));
            rows.add(row1);

        });
        //无问题人数
        List<DetailsStatica> noDetailStaticeById = problemDetailsMapper.findNoDetailStaticeById(id, termStatus);
        noDetailStaticeById.forEach(nodeta -> {
            Map<String, Object> row2 = new LinkedHashMap<>();
            Dept dept = deptMapper.selectOne(Wrappers.<Dept>lambdaQuery().eq(Dept::getDeptId, nodeta.getPid()));
            if (dept != null) {
                row2.put("单位", dept.getName() + "-" + nodeta.getDeptName());
            } else {
                row2.put("单位", nodeta.getDeptName());
            }
            row2.put("姓名", nodeta.getName());
            row2.put("警号", nodeta.getWarningsignal());
            row2.put("选项数量", 0);
            rows.add(row2);
        });

        //集合排序
        rows.sort((o1, o2) -> {
            String name1 = String.valueOf(o1.get("单位").toString());//name1是从你list里的第一个name
            String name2 = String.valueOf(o2.get("单位").toString()); //name2是从你list里的第二个name
            return name1.compareTo(name2);
        });

        String fileName = System.currentTimeMillis() + ".xlsx";
        // 合并单元格后的标题行，使用默认标题样式
        writer.merge(3, "交警支队个人选择问题项分析 ");
        // 一次性写出内容，使用默认样式，强制输出标题
        writer.write(rows, true);
        writer.setColumnWidth(-1, 23);
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition",
                "attachment;filename=\"" + URLEncoder.encode(fileName, "UTF-8") + "\"");
        ServletOutputStream out = response.getOutputStream();

        // 关闭writer，释放内存
        writer.flush(out, true);
        writer.close();
        IoUtil.close(out);

        return fileName;
    }


    //------------------------------------------------------------------------------------------------  PRIVATE METHOD

    /**
     * 设置标题别名
     *
     * @param writer 写出excel对象
     * @param state  状态码
     */
    private void setAlias(ExcelWriter writer, Integer state) {
        //自定义标题别名
        writer.addHeaderAlias("name", "警员姓名");
        writer.addHeaderAlias("userName", "警号");
        writer.addHeaderAlias("deptName", "部门");

        //自定义标题别名
//        if (1 == state || 3 == state) {
//            writer.addHeaderAlias("count", "问题数量");
//        }
        writer.addHeaderAlias("termName", "类型");
    }

    /**
     * 设置合并单元格
     *
     * @param questionnaireList 原始数据集合
     * @param exportLists       要导出的数据集合
     * @param deptName          部门名字
     */
    private void setMerge(List<Questionnaire> questionnaireList, List<Questionnaire> exportLists, String deptName) {
        if (ObjectUtils.isNotNull(questionnaireList)) {
            for (Questionnaire questionnaire : questionnaireList) {
                questionnaire.setParentName(deptName);
                questionnaire.setCount(questionnaireList.size());
                exportLists.add(questionnaire);
            }
        }
    }

    /**
     * 补全文件名
     *
     * @param deptId   部门id
     * @param list     数据集合
     * @param pId      父级id
     * @param fileName 文件名
     */
    private void setFileName(Integer deptId, List<?> list, Integer pId, StringBuilder fileName) {
        if (null == deptId) {
            return;
        }

        if (ObjectUtils.isNotNull(list) && 0 != pId) {
            Dept dept = deptMapper.selectById(pId);
            fileName.append(dept.getName()).append("-");
        }
    }


    /**
     * 按bean的属性值对list集合进行排序
     *
     * @param list         要排序的集合
     * @param propertyName 集合元素的属性名
     * @param isAsc        排序方向,true--正向排序,false--逆向排序
     * @return 排序后的集合
     */
    private List sortList(List list, String propertyName, boolean isAsc) {
        //借助commons-collections包的ComparatorUtils
        //BeanComparator，ComparableComparator和ComparatorChain都是实现了Comparator这个接口
        Comparator mycmp = ComparableComparator.getInstance();
        //允许null
        mycmp = ComparatorUtils.nullLowComparator(mycmp);
        if (isAsc) {
            //逆序
            mycmp = ComparatorUtils.reversedComparator(mycmp);
        }
        Comparator cmp = new BeanComparator<>(propertyName, mycmp);
        list.sort(cmp);
        return list;
    }

}
